-- 核对销售发货线上各单据中的数量
alter proc proc_czly_CheckSaleDelvQty(
    @date datetime,
    @cust_no varchar(100),
    @mtl_no varchar(100),
    @is_qty_diff int
) as
begin
set nocount on

if (isnull(@date, '1900-01-01')='1900-01-01' )
set @date = getdate()

--select @date

declare @FBegDt datetime,@FEndDt datetime
set @FBegDt=convert(varchar(7),@date,20)+'-01'
set @FEndDt=convert(varchar(10),dateadd(day,-1, dateadd(month,1,@FBegDt)),20)+' 23:59:59.997'

-- 提货联
select le.FMaterialId, m.FNumber, c.FCustId, c.FNumber FCustNo, cl.FName FCustName,SUM(le.FQty) FQtyLd
into #temp_l
from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
inner join T_BD_CUSTOMER_L cl on cl.FCustId=c.FCustId
inner join PAEZ_t_LadingEntry le on l.FID=le.FID and le.F_PAEZ_YWZF=0
inner join T_BD_MATERIAL m on m.FMaterialId=le.FMaterialId
--where l.FDocumentStatus='C' and l.FRepeatInvoice=0 and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
group by le.FMaterialId, m.FNumber, c.FCustId, c.FNumber, cl.FName


-- 发货汇总
select FMaterialId, FCustId, SUM(FQty) FQtySum 
into #temp_s
from (
    select distinct se.FEntryId, se.FMaterialId, c.FCustId, se.FQty
    --from PAEZ_t_Lading l
	from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
    inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
    inner join PAEZ_t_LadingEntry le on l.FID=le.FID and le.F_PAEZ_YWZF=0
    inner join PAEZ_SaleoutSumEntry_LK selk on selk.FSId=le.FEntryId and selk.FSTableName='PAEZ_t_LadingEntry'
    inner join PAEZ_SaleoutSumEntry se on se.FEntryId=selk.FEntryId
    inner join PAEZ_SaleoutSum s on s.FID=se.FID
    where s.FDocumentStatus='C' 
	--and l.FRepeatInvoice=0and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
) se
group by FMaterialId, FCustId

-- 发货通知
select FMaterialId, FCustId, SUM(FQty) FQtyDn 
into #temp_d
from (
    select distinct dne.FEntryId, dne.FMaterialId, c.FCustId, dne.FQty
    --from PAEZ_t_Lading l
	from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
    inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
    inner join PAEZ_t_LadingEntry le on l.FID=le.FID and le.F_PAEZ_YWZF=0
    inner join PAEZ_SaleoutSumEntry_LK selk on selk.FSId=le.FEntryId and selk.FSTableName='PAEZ_t_LadingEntry'
    inner join T_SAL_DELIVERYNOTICEENTRY_LK delk on delk.FSId=selk.FEntryID and delk.FSTableName='PAEZ_SaleoutSumEntry'
    inner join T_SAL_DELIVERYNOTICEENTRY dne on dne.FEntryId=delk.FEntryId
    inner join T_SAL_DELIVERYNOTICE dn on dn.FID=dne.FID
    where dn.FDocumentStatus='C' 
	--and l.FRepeatInvoice=0 and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
) dne
group by FMaterialId, FCustId

-- 销售出库
select FMaterialId, FCustId, SUM(FRealQty) FQtyOs 
into #temp_os
from (
    select distinct ose.FEntryId, ose.FMaterialId, c.FCustId, ose.FRealQty 
    --from PAEZ_t_Lading l
	from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
    inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
    inner join PAEZ_t_LadingEntry le on l.FID=le.FID and le.F_PAEZ_YWZF=0
    inner join PAEZ_SaleoutSumEntry_LK selk on selk.FSId=le.FEntryId and selk.FSTableName='PAEZ_t_LadingEntry'
    inner join T_SAL_DELIVERYNOTICEENTRY_LK delk on delk.FSId=selk.FEntryID and delk.FSTableName='PAEZ_SaleoutSumEntry'
    inner join T_SAL_OUTSTOCKENTRY_LK oselk on oselk.FSId=delk.FENTRYID and oselk.FSTableName='T_SAL_DELIVERYNOTICEENTRY'
    inner join T_SAL_OUTSTOCKENTRY ose on ose.FEntryId=oselk.FEntryId
    inner join T_SAL_OUTSTOCK os on os.FID=ose.FID
    where os.FDocumentStatus='C' 
	--and l.FRepeatInvoice=0 and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
) ose
group by FMaterialId, FCustId

-- 销售出库未审核数量
select FMaterialId, FCustId, SUM(FRealQty) FQtyOsNoC 
into #temp_osc
from (
    select distinct ose.FEntryId, ose.FMaterialId, c.FCustId, ose.FRealQty 
    --from PAEZ_t_Lading l
	from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
    inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
    inner join PAEZ_t_LadingEntry le on l.FID=le.FID and le.F_PAEZ_YWZF=0
    inner join PAEZ_SaleoutSumEntry_LK selk on selk.FSId=le.FEntryId and selk.FSTableName='PAEZ_t_LadingEntry'
    inner join T_SAL_DELIVERYNOTICEENTRY_LK delk on delk.FSId=selk.FEntryID and delk.FSTableName='PAEZ_SaleoutSumEntry'
    inner join T_SAL_OUTSTOCKENTRY_LK oselk on oselk.FSId=delk.FENTRYID and oselk.FSTableName='T_SAL_DELIVERYNOTICEENTRY'
    inner join T_SAL_OUTSTOCKENTRY ose on ose.FEntryId=oselk.FEntryId
    inner join T_SAL_OUTSTOCK os on os.FID=ose.FID
    where os.FDocumentStatus<>'C' 
	--and l.FRepeatInvoice=0 and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
) ose
group by FMaterialId, FCustId

-- 销售出库 不关联提货联数量
select FMaterialId, FCustId, SUM(FRealQty) FQtyOsNoR 
into #temp_osr
from (
    select distinct ose.FEntryId, ose.FMaterialId, c.FCustId, ose.FRealQty 
    from (select FID,FCustomerID from T_SAL_OUTSTOCK where FDate between @FBegDt and @FEndDt and FDocumentStatus='C')os
    inner join T_BD_CUSTOMER c on c.FCustId=os.FCustomerID
    inner join T_SAL_OUTSTOCKENTRY ose on ose.FID=os.FID
    --where os.FDocumentStatus='C' and year(os.FDate)=year(@date) and month(os.FDate)=month(@date)
) ose
group by FMaterialId, FCustId

-- 发票
select sce.FMaterialId, c.FCustId, SUM(sce.FPRICEQTY) FQtySc 
into #temp_sc
--from PAEZ_t_Lading l
from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
inner join T_BD_CUSTOMER c on c.FCustId=l.FCustomerID
inner join (
    select distinct FID, FEntryId, FMaterialId, FPRICEQTY, FDocumentStatus 
	from (
        select le.FID, le.FEntryId, sce.FMaterialId, sce.FPRICEQTY, sc.FDocumentStatus
        --from PAEZ_t_LadingEntry le
		from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
		inner join PAEZ_t_LadingEntry le on l.FID=le.FID
        inner join T_IV_SALESICENTRY_LK scelk on scelk.FSId=le.FEntryId and scelk.FSTableName='PAEZ_t_LadingEntry'
        inner join T_IV_SALESICENTRY sce on sce.FEntryId=scelk.FEntryId
        inner join T_IV_SALESIC sc on sc.FID=sce.FID
        inner join T_IV_SALESIC_O sco on sco.FID=sc.FID
        where le.F_PAEZ_YWZF=0
        union all 
        select le.FID, le.FEntryId, sce.FMaterialId, sce.FPRICEQTY, sc.FDocumentStatus
        --from PAEZ_t_LadingEntry le 
		from (select FID,FCustomerID from PAEZ_t_Lading where FDate between @FBegDt and @FEndDt and FDocumentStatus='C' and FRepeatInvoice=0)l
		inner join PAEZ_t_LadingEntry le on l.FID=le.FID
        inner join t_AR_receivableEntry_LK relk on relk.FSId=le.FEntryId and relk.FSTableName='PAEZ_t_LadingEntry'
        inner join T_IV_SALESICENTRY_LK scelk on scelk.FSId=relk.FEntryId and scelk.FSTableName='t_AR_receivableEntry'
        inner join T_IV_SALESICENTRY sce on sce.FEntryId=scelk.FEntryId
        inner join T_IV_SALESIC sc on sc.FID=sce.FID
        inner join T_IV_SALESIC_O sco on sco.FID=sc.FID
        where le.F_PAEZ_YWZF=0
    )t
) sce on l.FID=sce.FID
where sce.FDocumentStatus='C'
--and l.FRepeatInvoice=0 and year(l.FDate)=year(@date) and month(l.FDate)=month(@date)
group by sce.FMaterialId, c.FCustId

-- 非关联发票
select sce.FMaterialId, c.FCustId, SUM(sce.FPRICEQTY) FQtyScR 
into #temp_scr
--from T_IV_SALESIC sc
from(select FID,FCUSTOMERID from T_IV_SALESIC where FDate between @FBegDt and @FEndDt and FDocumentStatus='C')sc
inner join T_BD_CUSTOMER c on c.FCustId=sc.FCUSTOMERID
inner join T_IV_SALESICENTRY sce on sc.FID=sce.FID
--where sc.FDocumentStatus='C' and year(sc.FDate)=year(@date) and month(sc.FDate)=month(@date)
group by sce.FMaterialId, c.FCustId

select l.FMaterialId, l.FCustNo, l.FCustName, l.FNumber,
    FQtyLd, 
    isnull(FQtySum, 0) FQtySum, 
    isnull(FQtyDn, 0) FQtyDn, 
    isnull(FQtyOs, 0) FQtyOs, 
    isnull(FQtyOsNoC, 0) FQtyScNoC,
    isnull(FQtyOsNoR, 0) FQtyOsNoR,
    isnull(FQtySc, 0) FQtySc,
    isnull(FQtyScR, 0) FQtyScR
from #temp_l l 
left join #temp_s s on s.FMaterialId=l.FMaterialId and s.FCustId=l.FCustId
left join #temp_d d on d.FMaterialId=l.FMaterialId and d.FCustId=l.FCustId
left join #temp_os os on os.FMaterialId=l.FMaterialId and os.FCustId=l.FCustId
left join #temp_osc osc on osc.FMaterialId=l.FMaterialId and osc.FCustId=l.FCustId
left join #temp_osr osr on osr.FMaterialId=l.FMaterialId and osr.FCustId=l.FCustId
left join #temp_sc sc on sc.FMaterialId=l.FMaterialId and sc.FCustId=l.FCustId
left join #temp_scr scr on scr.FMaterialId=l.FMaterialId and scr.FCustId=l.FCustId
where (@is_qty_diff=0 or (
        isnull(FQtyOs, 0)<>isnull(FQtySc, 0) and isnull(FQtyOsNoR, 0)<>isnull(FQtySc, 0)
        and 
        isnull(FQtyOs, 0)<>isnull(FQtyScR, 0) and isnull(FQtyOsNoR, 0)<>isnull(FQtyScR, 0)
    )
)
and l.FCustNo like '%'+@cust_no+'%'
and l.FNumber like '%'+@mtl_no+'%'
order by l.FCustNo, l.FNumber

drop table #temp_l
drop table #temp_s
drop table #temp_d
drop table #temp_os
drop table #temp_osc
drop table #temp_osr
drop table #temp_sc
drop table #temp_scr

end
/*
exec proc_czly_CheckSaleDelvQty @date='#FDate#', 
    @cust_no='#FCustNo#', @mtl_no='#FNumber#', @is_qty_diff='#FQtyDiff#'
*/